/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;

/**
 *
 * @author Trang
 */
public class OrderDAO {
    private Connection con;

    public OrderDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }
    public List<Order> getAllOrder() {
        List<Order> result = new ArrayList<Order>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM ordertb";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idOrder = rs.getInt("idOrder");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");
                int totalQuantity = rs.getInt("totalQuantity");
                String orderStatus = rs.getString("orderStatus");
                Integer Customer_idCustomer = rs.getInt("Customer_idCustomer");
//                CustomerDAO cusDAO = new CustomerDAO();
//                Customer cus = new Customer();
//                cus = cusDAO.getCustomerFromCustomerID(Customer_idCustomer);
                
                Order order = new Order(idOrder, createTime, totalQuantity, totalAmount, orderStatus);
                result.add(order);
                }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public List<Order> getOrderByCreateStatus(int status) {
        List<Order> result = new ArrayList<Order>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM ordertb WHERE orderStatus = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            if(status == 1)
            ps.setString(1, "Finished");
            else if(status == 2)
                ps.setString(1, "Processing");
            else if (status == 3)
                ps.setString(1, "Paypal - Completed");
            else 
                ps.setString(1, "Shipped");
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idOrder = rs.getInt("idOrder");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");
                int totalQuantity = rs.getInt("totalQuantity");
                String orderStatus = rs.getString("orderStatus");
                Integer Customer_idCustomer = rs.getInt("Customer_idCustomer");
//                CustomerDAO cusDAO = new CustomerDAO();
//                Customer cus = new Customer();
//                cus = cusDAO.getCustomerFromCustomerID(Customer_idCustomer);
                
                Order order = new Order(idOrder, createTime, totalQuantity, totalAmount, orderStatus);
                result.add(order);
                }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public Order getOrderbyID(Integer oid)
    {
//        String sortname;
//       if(sort ==2) sortname="totalQuantity"; 
//       if(sort == 3)     sortname = "totalAmount";
       //else sortname = "createTime";
        Order result = new Order();
        try {
            //get parent categories
            String sqlst = "SELECT * FROM ordertb WHERE idOrder = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, oid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idOrder = rs.getInt("idOrder");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");
                int totalQuantity = rs.getInt("totalQuantity");
                String orderStatus = rs.getString("orderStatus");
                Integer Customer_idCustomer = rs.getInt("Customer_idCustomer");
                CustomerDAO cusDAO = new CustomerDAO();
                Customer cus = new Customer();
                cus = cusDAO.getCustomerFromCustomerID(Customer_idCustomer);
                
                //result.setCustomer(cus);
                result = new Order(cus,idOrder, createTime, totalQuantity, totalAmount, orderStatus);
                }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public List<Orderitem> getOrderitemByOrderId(Integer oid)
    {
        List<Orderitem> result = new ArrayList<Orderitem>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM orderitem WHERE Order_idOrder= ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1,oid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                double orderPrice = rs.getDouble("orderPrice");
                int orderQuantity = rs.getInt("orderQuantity");
                
                ProductDAO productDAO = new ProductDAO();
                Product pro = new Product();
                pro = productDAO.getProductFromProductId(Product_idProduct);
                 
                Orderitem item = new Orderitem(orderQuantity, orderPrice, pro);
                
                result.add(item);
                }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public boolean updateOrderStatus(Integer oid){
        boolean result = false;
        
        try {
            String sqlst = "UPDATE ordertb "
                    + "SET orderStatus = ? "
                    + "WHERE idOrder = ?";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            
            ps.setString(1, "Shipped");
            ps.setInt(2, oid);
            //System.out.println(sqlst);
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
    
     public boolean updateOrderStatus(Integer oid, int status){
        boolean result = false;
        
        try {
            String sqlst = "UPDATE ordertb "
                    + "SET orderStatus = ? "
                    + "WHERE idOrder = ?";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            if(status == 1)
                ps.setString(1,"Finished");
            else ps.setString(1, "Shipped");
            ps.setInt(2, oid);
            //System.out.println(sqlst);
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
     
     public boolean UpdateProductStockQuantity(Product product, int newQuantity)
      {
          boolean result = false;
          try
          {
              String sql = "UPDATE product "
                      +"SET stockQuantity = ? "
                      +"WHERE idProduct = ?";
              PreparedStatement ps = con.prepareStatement(sql);
              ps.setInt(1, newQuantity);
              ps.setInt(2, product.getIdProduct());
              ps.executeUpdate();
              
              result = true;
          }
          catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
          return result;
      }
}
